﻿
#Region " I m p o r t s "
Imports System.Data.SQLite
#End Region

Public Class DB

#Region " Private Sub: EanBekannt () "
    Public Shared Function EanBekannt(EAN As String) As Integer
        Dim tmpDIC As New Dictionary(Of Integer, Integer)

        Dim tmpSQL As String = "SELECT " & TBL.EANcodes.Id &
            " FROM " & TBL.EANcodes.TBL &
            " WHERE " & TBL.EANcodes.Code & " = '" & EAN & "'"
        Dim tmpDT As DataTable = BildeDataSet(tmpSQL).Tables(0)
        If tmpDT.Rows.Count = 1 Then
            Return CInt(tmpDT.Rows(0)(TBL.EANcodes.Id))
        Else
            Return -1
        End If
    End Function
#End Region

#Region " BildeDataSet "
    Public Shared Function BildeDataSet(SqlQuery As String) As DataSet
        Try
            Dim connect As New SQLiteConnection()
            Dim adapter As New SQLiteDataAdapter(SqlQuery, connect)
            Dim tmpDataSet As New DataSet
            connect.ConnectionString = "Data Source=VorratsDaten.s3db;"
            connect.Open()
            adapter.Fill(tmpDataSet)
            adapter.Dispose()
            connect.Close()
            Return tmpDataSet
        Catch ex As Exception
            Throw
        End Try
    End Function
#End Region

#Region " InsertDeleteUpdate "
    Public Shared Sub InsertDeleteUpdate(SqlQuery As String)
        Dim connect As New SQLiteConnection()
        Dim command As SQLiteCommand
        connect.ConnectionString = "Data Source=VorratsDaten.s3db;"
        connect.Open()
        command = connect.CreateCommand
        command.CommandText = SqlQuery
        command.ExecuteNonQuery()
        command.Dispose()
        connect.Close()
    End Sub
#End Region

#Region " HoleErsteZeileErsteSpalte "
    Public Shared Function HoleErsteZeileErsteSpalte(SqlQuery As String) As Object
        Try
            Dim connect As New SQLiteConnection()
            Dim command As SQLiteCommand
            Dim retVar As Object
            connect.ConnectionString = "Data Source=VorratsDaten.s3db;"
            connect.Open()
            command = connect.CreateCommand
            command.CommandText = SqlQuery
            retVar = command.ExecuteScalar()
            command.Dispose()
            connect.Close()
            Return retVar
        Catch ex As Exception
            Throw
        End Try
    End Function
#End Region

#Region " Public Shared Function: HoleNächstenFreieID () "
    Public Shared Function HoleNächstenFreieID(TabellenName As String) As Integer
        Dim tmpSQL As String = "SELECT MAX(id) FROM " & TabellenName
        Dim result As String = HoleErsteZeileErsteSpalte(tmpSQL).ToString
        If result = "" Then
            Return 1
        Else
            Return CInt(result) + 1
        End If
    End Function
#End Region

End Class